{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 公众号：数据不吹牛，更多案例和有趣分析等你来撩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.chdir('C:\\\\Users\\\\Administrator\\\\Desktop\\\\JC数据集')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>产品ID</th>\n",
       "      <th>价格</th>\n",
       "      <th>总销量</th>\n",
       "      <th>总评价数</th>\n",
       "      <th>规格类型</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59497802</td>\n",
       "      <td>189.0</td>\n",
       "      <td>22153</td>\n",
       "      <td>12269</td>\n",
       "      <td>套装</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55594403</td>\n",
       "      <td>95.0</td>\n",
       "      <td>227064</td>\n",
       "      <td>53842</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56419172</td>\n",
       "      <td>79.0</td>\n",
       "      <td>733418</td>\n",
       "      <td>130106</td>\n",
       "      <td>正常规格</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>58567235</td>\n",
       "      <td>89.0</td>\n",
       "      <td>480040</td>\n",
       "      <td>103975</td>\n",
       "      <td>常规单品</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53625235</td>\n",
       "      <td>59.0</td>\n",
       "      <td>253606</td>\n",
       "      <td>49611</td>\n",
       "      <td>常规单品</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       产品ID     价格     总销量    总评价数  规格类型\n",
       "0  59497802  189.0   22153   12269    套装\n",
       "1  55594403   95.0  227064   53842   NaN\n",
       "2  56419172   79.0  733418  130106  正常规格\n",
       "3  58567235   89.0  480040  103975  常规单品\n",
       "4  53625235   59.0  253606   49611  常规单品"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel('刷单鉴定评价数据.xlsx')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算评销比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>产品ID</th>\n",
       "      <th>价格</th>\n",
       "      <th>总销量</th>\n",
       "      <th>总评价数</th>\n",
       "      <th>规格类型</th>\n",
       "      <th>评销比</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>59497802</td>\n",
       "      <td>189.0</td>\n",
       "      <td>22153</td>\n",
       "      <td>12269</td>\n",
       "      <td>套装</td>\n",
       "      <td>55.383018</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>55594403</td>\n",
       "      <td>95.0</td>\n",
       "      <td>227064</td>\n",
       "      <td>53842</td>\n",
       "      <td>NaN</td>\n",
       "      <td>23.712257</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>56419172</td>\n",
       "      <td>79.0</td>\n",
       "      <td>733418</td>\n",
       "      <td>130106</td>\n",
       "      <td>正常规格</td>\n",
       "      <td>17.739679</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>58567235</td>\n",
       "      <td>89.0</td>\n",
       "      <td>480040</td>\n",
       "      <td>103975</td>\n",
       "      <td>常规单品</td>\n",
       "      <td>21.659653</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>53625235</td>\n",
       "      <td>59.0</td>\n",
       "      <td>253606</td>\n",
       "      <td>49611</td>\n",
       "      <td>常规单品</td>\n",
       "      <td>19.562234</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       产品ID     价格     总销量    总评价数  规格类型        评销比\n",
       "0  59497802  189.0   22153   12269    套装  55.383018\n",
       "1  55594403   95.0  227064   53842   NaN  23.712257\n",
       "2  56419172   79.0  733418  130106  正常规格  17.739679\n",
       "3  58567235   89.0  480040  103975  常规单品  21.659653\n",
       "4  53625235   59.0  253606   49611  常规单品  19.562234"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['评销比'] = df['总评价数'] / df['总销量'] * 100\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 查看评销比分布"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.text.Text at 0x99d2eb8>"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAlMAAAFICAYAAAB0uHstAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAE8ZJREFUeJzt3X+w5Xdd3/HXmywkDalRklWakmwwTGlMoHZM0Vo6hKLEgGOnQYOxNKFDXRDidIqUWqstiLY4oNLAwHhTCejwK4gRo5CpMyF1YqF0M9AGQnBi3JANQ1yNSPPTJvvuH+e79HDZuDf7uT/OPffxmDmTe77nnHs+73t3d575nvM93+ruAABwbB631QsAANjOxBQAwAAxBQAwQEwBAAwQUwAAA8QUAMAAMQUAMEBMAQAMEFMAAAN2beaTnXrqqX3mmWdu5lMCAByTm2666U+7e/fR7repMXXmmWdm3759m/mUAADHpKruWMv9vMwHADBATAEADBBTAAADxBQAwAAxBQAwQEwBAAwQUwAAA8QUAMAAMQUAMEBMAQAMEFMAAAM29dx87GArK1u9gpm9e7d6BQAsGXumAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYsOaYqqpfrKquqlOr6viquqqq7qqqW6vqoo1cJADAotq1ljtV1fOSnDG36SeTnJzk9CTnJLmxqm7o7nvWf4kAAIvrqHumquqbkrwpyeVzmy9OckV3H+rum5PsS3LBozx+b1Xtq6p9Bw8eXI81AwAsjLW8zPeOJK/v7rvntp2V5Pa563ckOe1ID+7ule4+r7vP271797GvFABgAf2VMVVVL0lyX3d/eNVNh6bLYb3qOgDAjnC090y9MsnJVfXpuW0fS3J3Zu+hOjBt25Pkd9Z/eQAAi+2vjKnu/u7561XVSZ6b5F8nubyqPp7k3CRnJ7luoxYJALCojvVzpt6QpJLsT3J1kku7+4H1WhQAwHaxpo9GOKy7a+7qJeu8FgCAbccnoAMADBBTAAADxBQAwAAxBQAwQEwBAAx4TEfzwba3srLVK5jZu3erVwDAOrFnCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWuKqap6X1V9rqoOVNXbquq4qjq+qq6qqruq6taqumijFwsAsGh2rfF+r+3uO6vqiUk+k+SaJM9OcnKS05Ock+TGqrqhu+/ZmKUCACyeNe2Z6u47py9PS3IoyR8muTjJFd19qLtvTrIvyQWrH1tVe6tqX1XtO3jw4DotGwBgMaz1Zb4XV9WBJJ9K8nNTXJ2V5Pa5u92RWWx9je5e6e7zuvu83bt3r8eaAQAWxppe5uvuDyT5QFV9a5Jrq+orme2hOjR/t1XXAQCW3mM6mq+7b0/ywSTnJzmQ5Iy5m/ck2b9eCwMA2A6OGlNVdXpVPW36+uQkFyb5eGZvQr+8Zp6R5Owk123kYgEAFs1aXuY7McmHpiP5Hkryru5+b1X9dpIrM9sbdX+SS7v7gQ1bKQDAAjpqTHX355Oce4Tt9ya5ZCMWBQCwXfgEdACAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAG7tnoBbLCVla1eAQAsNXumAAAGiCkAgAFiCgBggJgCABggpgAABogpAIABYgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABiwa6sXADvSyspWryDZu3erVwCwFOyZAgAYIKYAAAYcNaZq5o1VdWtV7a+qj1TV7qo6vqquqqq7ptsu2owFAwAskrW8Z6qSfCHJuUkeSXJVkp9Ock+Sk5OcnuScJDdW1Q3dfc8GrRUAYOEcdc9Udx/q7rd398Pd3Un2JTklycVJrphuv3nafsHGLhcAYLE8pvdMVdXjk1yW5D1Jzkpy+9zNdyQ57QiP2VtV+6pq38GDB0fWCgCwcNYcU1X1uCTvTPKx7v5okkPT5bBedX22sXulu8/r7vN27949ul4AgIWyppiqql2Z7Y062N2vnTYfSHLG3N32JNm/rqsDAFhwazma7/gkv5nklu5+9dxN1yS5fDra7xlJzk5y3cYsEwBgMa3laL4fTXJhkm+rqsumbR9K8oYkV2a2N+r+JJd29wMbsUgAgEV11Jjq7rcleduj3HzJ+i4HAGB7cW4+2KkW4fyAiXMEAtue08kAAAwQUwAAA8QUAMAAMQUAMEBMAQAMEFMAAAPEFADAADEFADBATAEADBBTAAADxBQAwAAxBQAwQEwBAAwQUwAAA8QUAMAAMQUAMEBMAQAMEFMAAAPEFADAADEFADBATAEADBBTAAADxBQAwAAxBQAwQEwBAAwQUwAAA8QUAMAAMQUAMEBMAQAMEFMAAAPEFADAADEFADBATAEADBBTAAADxBQAwAAxBQAwQEwBAAwQUwAAA8QUAMAAMQUAMEBMAQAMEFMAAAPEFADAADEFADBATAEADBBTAAADxBQAwAAxBQAwYM0xVVXHVdWzNnIxAADbzVFjqqp2VdXVSb6U5Pq57cdX1VVVdVdV3VpVF23kQgEAFtFa9kx1kl9Pcv6q7T+Z5OQkpyf5oSRXVdWT1nV1AAAL7qgx1d2PdPe1Se5bddPFSa7o7kPdfXOSfUku2IA1AgAsrF0Djz0rye1z1+9IctrqO1XV3iR7k+SMM84YeLptZmVlq1cA28Oi/F3Zu3erVwBsUyNH8x2aLof1quuzjd0r3X1ed5+3e/fugacDAFg8IzF1IMn8rqY9SfYPrQYAYJsZialrklxeM89IcnaS69ZnWQAA28Oa3jNVVbcmOSHJiVV1W5JbkvxIkisz2xt1f5JLu/uBDVonAMBCWlNMdffffpSbLlnHtQAAbDtOJwMAMEBMAQAMEFMAAAPEFADAADEFADBATAEADBBTAAADxBQAwAAxBQAwQEwBAAwQUwAAA8QUAMAAMQUAMEBMAQAMEFMAAAPEFADAADEFADBATAEADBBTAAADxBQAwAAxBQAwQEwBAAwQUwAAA3Zt9QIAFsLKylavYLHs3bvVK4Btw54pAIABYgoAYICYAgAYIKYAAAaIKQCAAY7mA+DrLcrRjY4qZBuwZwoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBggJgCABggpgAABiznufkW5ZxSAIxZhH/PF+X8gIvws0gW5+exQOyZAgAYIKYAAAaIKQCAAWIKAGCAmAIAGLCcR/MBwHpZlKPoFsWi/DwW6KhCe6YAAAaIKQCAAUMxVVXPq6rPVdUXquqDVfXX12thAADbwTHHVFWdnOQDSS5NsifJ/03yM+u0LgCAbWFkz9Tzk3ymu/9nd3eSdyT5x+uzLACA7WHkaL6zktw+d/2OJKetvlNV7U1y+C3391bV5weec61OTfKnm/A8i8bcO4u5dxZz7yzmPpqXv3xjVzKzZy13GompQ9PlsF51fbaxeyXJph5HWVX7uvu8zXzORWDuncXcO4u5dxZzby8jL/MdSHLG3PU9SfYPrQYAYJsZianrkjyrqp5ZVZXkx5L8+vosCwBgezjmmOrue5L8syRXJ7kzyV8muWKd1jVqQT6eddOZe2cx985i7p3F3NtIzQ7EAwDgWPgEdACAAWJqG6uq46rqWVu9DgDYyZYqpnbK6W2qaldVXZ3kS0mun9t+fFVdVVV3VdWtVXXR1q1y/dXMG6fZ9lfVR6pq97LPnSRV9b7pz/aBqnrbFNJLP3eSVNUvVlVX1ak7YeaqumGa77bp8n3T9p0w+xOq6i3TjPur6uXLPndVvWbud31bVf1JVX1i2edOkqp6YVX976r6o6r6/ap66nade2neMzWd3uaPklyYZF+S9yQ50N2v3dKFbYCqOi7JCzL70NT/0d0nTdv/Q5K/k+QHk5yT5MYkT50OFtj2qupxSV6R2RsUH0lyVZK/SHJPlnjuJKmq07v7zqp6YpLPJPkXSZ6d5Z/7eZn9zn8wye4kr8ryz3xDktd19w2rti/13+8kqapfTvLNSV7W3Q9Of95fkyWfe15VvS/J7yU5PUs8d1XtSvLnSc7v7puq6k2Z/R3/42zDuZdpz9SOOb1Ndz/S3dcmuW/VTRcnuaK7D3X3zZlF5QWbvsANMs319u5+ePod70tySpZ87iTp7junL0/L7MNx/zBLPndVfVOSNyW5fG7zUs98FEs9e1WdmuQlSV7V3Q8mSXfflyWfe15V/c0k5yd5b5Z/7k7yUP7/h32fkOSL2aZzL1NMren0Nktux/wMqurxSS7LbA/k0s9dVS+uqgNJPpXk56a4Wva535Hk9d1999y2ZZ85mX3MzLunl3XfXFVPmLYv++znZjbTG6vq81X1e1X19Cz/3PNemeTdU0wu9dzd/UiS70/ynmlv7ClJfjbbdO5liqk1nd5mye2In8H0ct87k3ysuz+aHTB3d3+gu5+S5JlJXlNVL8oSz11VL0lyX3d/eNVNSzvzYd39/O7ek+Q5Sb4zyU9MNy377E9O8vQk/6W7n57ZS13vzvLPnSSpqhOSvCyz/4lIlnzu6d/xH8/sbRuvyuz3/4Js07mXKaac3mYH/Aym19nfk+Tg3Pvhln7uw7r79iQfzOylgGWe+5VJvquqPl1Vn562fSzJ3Vnemb9Gd/9JZh+K/Mxp0zL/vpPZATW3dfe+6fr7M9tbtexzH/ZPk3y8u++Yri/73M9PcmZ3v6W7P5vktUl+Kdt07mWKKae3Sa5Jcvl01Nszkpyd2c9lKVTV8Ul+M8kt3f3quZuWfe7Tq+pp09cnZ3aQxcezxHN393d39znd/e3d/e3T5udmFhdLOfNh00tbh3/XP5TkD6ablvb3PflEklOq6vBJbl+UJf9zvsq/TPLWuevLPvcDSb61qp48Xf/OJH+WbTr3rq1ewHrp7nuq6vDpbU7KbBfxopzeZt1V1a2ZvWHvxKq6LcktSX4kyZWZVfz9SS7t7ge2bJHr70czC4lvq6rLpm0fSvKGLPfcJyb50HRk00NJ3tXd762q385yz30ky/67TpKrp5B6OLN/z94+bV/q2aej916cZKWqviHJbZm97HVPlnjuJKmq5yY5rruvn9u87L/v/1ZVv5Lkk1X1UJK7krw0s6P5tt3cS/PRCAAAW2GZXuYDANh0YgoAYICYAgAYIKYAAAaIKQCAAWIKAGCAmAIAGCCmgE1VVY+rqh+vqhMf4+N2VdXNVXXGdP2fTB9umap6RVW9f/q6quqiNXy/A1V1/jGMAPA1xBSw2Z6Q5IeTfLiqjp9CqI9yeUpmJ0H9Snd/Yfo+fz/JNVX1hFXf/6eSvHkutG6sqpdu0mzADiSmgE3V3Q8m+YEkT0vyH5P8SpLHz10+ktlZ5L+6rbsPJHlFku+oqi9X1ZeT/PT0LX/i8PeuquckeXWSH+juv3isa6uql1XVtxzrbMDOJKaATdfdf5bkhUl+Psk/SPLO7n64ux9O0kkOTV+fmuT6KZKe1N0ndPc3Tpe/TPLizM40f9j/SnJhd3/msa6pqn4qyZvztWesBziqpTnRMbC9dPctSVJVn07yvVX1wu7+3VV3e12SLyf5N5md+PWrquoVme2tSpJTMjvB+Q3TbV/s7hfM3f3xVXXC3PWHVn2vn0nyr5J8T3ffNDAWsAOJKWBTVdUNSZ6dJN29q7vvrarXJ3lrVV0/d79nJXlJknOTPCXJ66rq2iT3Jvn9JPuSfCnJdaue4klJLl21bWW6HPYPp/+eVFW/keS7kjyvuz81PiGw04gpYFN19/lVdVKS/zO3+VeT/I0kD89tOynJv+vu/Un2J/meqjqQ5Nndvb+qXpfkE939lvnvX1Vn5utj6p9397tW3S9Jfi3JzUm+o7vvHpkL2LnEFLBlalY0T5yuvinJ8UmOm/77ySSfnMLrUHfff4Rv8dIjfLzBCUe436P5jSSvnN6fBXBMxBSwlfYk+eMjbP++JPN7nB6sqkeSnJjks1XVmb1Z/BNJ3r/qsd+c2ccjrMV7hRQwytF8wJbp7v3dXfOXJL+b5MdWbf9r3X1Ski8mOWf6Oklu7e7fmr8k+a/Hup6q+paq+obxyYCdREwBW6qq/t7Aw/99VT08f0ly21Ge7/lV9Y2PcvOLknx0YD3ADiSmgK2wK0mq6peSvPsxPK6S7Kmqw0fm/ex0ROBXL5l9GOjXP7Dqwqr670l+K7M3tx/JaUm+8Ci3ARyRmAK2wnOm/56S2ccSHFVV7Uny5MzeI/XZI9z+t6b7nJ/kwWnbrsw+VuGXk7wrybVJnjJ9ovq9Sf7u3ONPTvKPknzuWAYCdi4xBWyFP0hySXdf1t1fWeNjDiT5t0me3t3/+Qi3X5nkU0l+Ick7pm27khxM8tYkT+3u/9Td90y3vT3Jzx8+/1+SP89sj9WvHtNEwI5V3b3VawDYMFV1XHc/stXrAJaXmAIAGOBlPgCAAWIKAGCAmAIAGCCmAAAGiCkAgAFiCgBgwP8DRUhe7fIwIj8AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x99d22e8>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "fig,ax = plt.subplots(1,1,figsize = (10,5))\n",
    "sns.distplot(df['评销比'],color = 'red',kde = False)\n",
    "\n",
    "plt.yticks(fontsize=11)\n",
    "plt.xticks(fontsize=11)\n",
    "\n",
    "ax.set_xlabel('评销比', fontsize=14)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 判断是否有刷单嫌疑"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    166\n",
       "True      22\n",
       "Name: 是否有刷单嫌疑, dtype: int64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['是否有刷单嫌疑'] = df['评销比'] > 40\n",
    "df['是否有刷单嫌疑'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 导入评论数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家</th>\n",
       "      <th>初评内容</th>\n",
       "      <th>评价日期</th>\n",
       "      <th>追评</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>摈**唉</td>\n",
       "      <td>昨天晚上用了一次， 姜味很浓，用过一段时间再看看效果吧，好用会再回购的！</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>t**4</td>\n",
       "      <td>最近脱发特别严重，鬓角的头发最是损失惨重，抱着试试看的态度来的，目前我用了1个疗程感觉恢复得...</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>露**发</td>\n",
       "      <td>最近头发大把大把的脱，特别是洗头的时候！刚开始是抱着试试的心态，每次都会隔断时间拍照自己对比...</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>t**6</td>\n",
       "      <td>质量很好，效果不错</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>去**5</td>\n",
       "      <td>这次放假回家看到老爸的大脑门，莫名的揪心，老爸为家庭操心了太多，头发一直在掉，这次买了这款防...</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     买家                                               初评内容       评价日期 追评\n",
       "0  摈**唉               昨天晚上用了一次， 姜味很浓，用过一段时间再看看效果吧，好用会再回购的！ 2019-11-29  -\n",
       "1  t**4  最近脱发特别严重，鬓角的头发最是损失惨重，抱着试试看的态度来的，目前我用了1个疗程感觉恢复得... 2019-11-29  -\n",
       "2  露**发  最近头发大把大把的脱，特别是洗头的时候！刚开始是抱着试试的心态，每次都会隔断时间拍照自己对比... 2019-11-29  -\n",
       "3  t**6                                          质量很好，效果不错 2019-11-29  -\n",
       "4  去**5  这次放假回家看到老爸的大脑门，莫名的揪心，老爸为家庭操心了太多，头发一直在掉，这次买了这款防... 2019-11-29  -"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments = pd.read_excel('生姜防脱洗发水.xlsx')\n",
    "comments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 评价长度筛选"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1200, 5)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家</th>\n",
       "      <th>初评内容</th>\n",
       "      <th>评价日期</th>\n",
       "      <th>追评</th>\n",
       "      <th>评价长度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>摈**唉</td>\n",
       "      <td>昨天晚上用了一次， 姜味很浓，用过一段时间再看看效果吧，好用会再回购的！</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>t**4</td>\n",
       "      <td>最近脱发特别严重，鬓角的头发最是损失惨重，抱着试试看的态度来的，目前我用了1个疗程感觉恢复得...</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "      <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>露**发</td>\n",
       "      <td>最近头发大把大把的脱，特别是洗头的时候！刚开始是抱着试试的心态，每次都会隔断时间拍照自己对比...</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "      <td>85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>去**5</td>\n",
       "      <td>这次放假回家看到老爸的大脑门，莫名的揪心，老爸为家庭操心了太多，头发一直在掉，这次买了这款防...</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "      <td>76</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>德**艺</td>\n",
       "      <td>以前就用过这款生姜洗发水防脱发效果真的很好，这次这个疗程是买来巩固的用过之后脱发已经很少了，...</td>\n",
       "      <td>2019-11-29</td>\n",
       "      <td>-</td>\n",
       "      <td>60</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     买家                                               初评内容       评价日期 追评  评价长度\n",
       "0  摈**唉               昨天晚上用了一次， 姜味很浓，用过一段时间再看看效果吧，好用会再回购的！ 2019-11-29  -    36\n",
       "1  t**4  最近脱发特别严重，鬓角的头发最是损失惨重，抱着试试看的态度来的，目前我用了1个疗程感觉恢复得... 2019-11-29  -    80\n",
       "2  露**发  最近头发大把大把的脱，特别是洗头的时候！刚开始是抱着试试的心态，每次都会隔断时间拍照自己对比... 2019-11-29  -    85\n",
       "4  去**5  这次放假回家看到老爸的大脑门，莫名的揪心，老爸为家庭操心了太多，头发一直在掉，这次买了这款防... 2019-11-29  -    76\n",
       "5  德**艺  以前就用过这款生姜洗发水防脱发效果真的很好，这次这个疗程是买来巩固的用过之后脱发已经很少了，... 2019-11-29  -    60"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments['评价长度'] = comments['初评内容'].apply(len)\n",
    "comments = comments.loc[comments['评价长度'] > 15,:]\n",
    "print(comments.shape)\n",
    "comments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 按内容排序，找到嫌疑评价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>买家</th>\n",
       "      <th>初评内容</th>\n",
       "      <th>评价日期</th>\n",
       "      <th>追评</th>\n",
       "      <th>评价长度</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1307</th>\n",
       "      <td>你**个</td>\n",
       "      <td>感觉越洗头发掉得越多，每次洗必须要用洗发水两次以上，还要搓按5分钟，这样洗下去头发本来就少，...</td>\n",
       "      <td>2019-07-11</td>\n",
       "      <td>我是短发，洗一次掉这么多，以前洗只掉几根，洗了之后头痒的要死，当初客服说用了不适应可以退，现...</td>\n",
       "      <td>348</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1147</th>\n",
       "      <td>y**8</td>\n",
       "      <td>使用了第二次才来评价的，我头发很长（齐膝）掉得特别厉害。之前使用防脱洗发水用完之后呢换成了潘...</td>\n",
       "      <td>2019-09-02</td>\n",
       "      <td>长头发的妹子可以试试这款洗发水哦！我现在掉发已经开始在变少了，开心</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>629</th>\n",
       "      <td>0**b</td>\n",
       "      <td>1客服小海马说寄来的品牌是柏诗春天，我下单购买的海洋诗韵，俩不同品牌都是一个厂家生产的，让我...</td>\n",
       "      <td>2019-10-22</td>\n",
       "      <td>-</td>\n",
       "      <td>290</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>151</th>\n",
       "      <td>t**1</td>\n",
       "      <td>自从高考那个紧张的阶段后，我的头发就很会掉，每天都房间里，床铺上地上都可以看到我的掉发，每次...</td>\n",
       "      <td>2019-11-21</td>\n",
       "      <td>-</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>587</th>\n",
       "      <td>女**8</td>\n",
       "      <td>自从高考那个紧张的阶段后，我的头发就很会掉，每天都房间里，床铺上地上都可以看到我的掉发，每次...</td>\n",
       "      <td>2019-10-24</td>\n",
       "      <td>-</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>674</th>\n",
       "      <td>e**1</td>\n",
       "      <td>自从高考那个紧张的阶段后，我的头发就很会掉，每天都房间里，床铺上地上都可以看到我的掉发，每次...</td>\n",
       "      <td>2019-10-16</td>\n",
       "      <td>-</td>\n",
       "      <td>177</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        买家                                               初评内容       评价日期  \\\n",
       "1307  你**个  感觉越洗头发掉得越多，每次洗必须要用洗发水两次以上，还要搓按5分钟，这样洗下去头发本来就少，... 2019-07-11   \n",
       "1147  y**8  使用了第二次才来评价的，我头发很长（齐膝）掉得特别厉害。之前使用防脱洗发水用完之后呢换成了潘... 2019-09-02   \n",
       "629   0**b  1客服小海马说寄来的品牌是柏诗春天，我下单购买的海洋诗韵，俩不同品牌都是一个厂家生产的，让我... 2019-10-22   \n",
       "151   t**1  自从高考那个紧张的阶段后，我的头发就很会掉，每天都房间里，床铺上地上都可以看到我的掉发，每次... 2019-11-21   \n",
       "587   女**8  自从高考那个紧张的阶段后，我的头发就很会掉，每天都房间里，床铺上地上都可以看到我的掉发，每次... 2019-10-24   \n",
       "674   e**1  自从高考那个紧张的阶段后，我的头发就很会掉，每天都房间里，床铺上地上都可以看到我的掉发，每次... 2019-10-16   \n",
       "\n",
       "                                                     追评  评价长度  \n",
       "1307  我是短发，洗一次掉这么多，以前洗只掉几根，洗了之后头痒的要死，当初客服说用了不适应可以退，现...   348  \n",
       "1147                  长头发的妹子可以试试这款洗发水哦！我现在掉发已经开始在变少了，开心   290  \n",
       "629                                                   -   290  \n",
       "151                                                   -   177  \n",
       "587                                                   -   177  \n",
       "674                                                   -   177  "
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "comments = comments.sort_values(['评价长度','初评内容'],ascending = False)\n",
    "comments.head(6)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 统计重复评价数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "总评价数： 1200\n",
      "重复的评价数占比：31.5%\n"
     ]
    }
   ],
   "source": [
    "#按内容分组，统计每条评价出现的次数\n",
    "filt = comments.groupby('初评内容')['买家'].count().reset_index()\n",
    "filt.columns = ['初评内容','重复次数']\n",
    "\n",
    "#统计重复评价出现的次数\n",
    "reap = filt.loc[filt['重复次数'] > 1,'重复次数'].sum()\n",
    "\n",
    "print('总评价数：',len(comments))\n",
    "print('重复的评价数占比：{}%'.format(reap / len(comments) * 100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
